package com.poi.action;

import com.opensymphony.xwork2.ActionContext;
import db_tool.LogTool;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.struts2.ServletActionContext;
import org.apache.struts2.StrutsStatics;
import tree_util.Account;

import javax.servlet.http.HttpServletRequest;
import java.sql.*;
import java.util.Map;

public class DominantPhytoplanktonDao {
	Account account=(Account) ServletActionContext.getRequest().getSession().getAttribute("account");
	HttpServletRequest request=(HttpServletRequest)ActionContext.getContext().get(StrutsStatics.HTTP_REQUEST);
	private DbBean db = new DbBean();
	Connection conn = db.getConnection();
	private String sql = null;
	private String sql2 = null;
	Statement st = null;
	@SuppressWarnings("finally")
	public void saveUser(){
		int rowCounterMoniteringSite = 0;
		int rowCounterFractureSurface = 0;
		int rowCounterPhytoplankton = 0;
		int rowCounterDominantPhytoplanktonSpecies = 0;
		Connection conn = null;
		PreparedStatement ps = null;
		boolean flag = false;
		boolean flag2=false;
		parseExcelUtil peu = new  parseExcelUtil();
		DominantPhytoplanktonDao dao = new DominantPhytoplanktonDao();
		DominantPhytoplankton dz= new DominantPhytoplankton();
		DominantPhytoplankton dzf = new DominantPhytoplankton();
		int count = peu.getListDatas().size()+1;
		for (int i = 0; i < peu.getListDatas().size(); i++) {
			request.getSession().setAttribute("massage", "共有"+count+"条数据,已导入数据库"+i+"条,请等待操作,不要关闭浏览器");			
			Map excelCol = (Map)peu.getListDatas().get(i);
			try {					
				BeanUtils.populate(dz, excelCol);	
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
		    String mon_name = dz.getMon_name();
		    String mon_site = dz.getMon_site();
		    String mon_river = dz.getMon_river();
		    String mon_time = dz.getMon_time();
		    double mon_ltude = dz.getMon_ltude();
		    double mon_atude = dz.getMon_atude();
		    String mon_wht = dz.getMon_wth();
		    double mon_tmpt = dz.getMon_tmpt();
		    String fs_pst = dz.getFs_pst();
		    int fs_dtb = dz.getFs_dtb();
			String pph_name = dz.getPph_name();
			int pph_q = dz.getPph_q();
			double pph_bl = dz.getPph_bl(); 	
			System.out.println(mon_name+mon_site+mon_river+mon_time+mon_ltude+mon_atude+mon_tmpt+fs_pst+pph_name+pph_q+pph_bl);
			String[] ms_phs=mon_time.split("\\ ");
			String[] ymd=ms_phs[0].split("-");	
		    String[] time = ms_phs[1].split(":");
			flag=false;
			String mon_id="MON"+ ymd[0]+ymd[1]+ymd[2]+time[0]+time[1]+time[2];
			sql = "select * from MonitoringSite where InverstigationID='"+mon_id+"';";
			sql2 ="select * from  MonitoringSite where InverstigationID='"+mon_id+"' and Site='"+mon_site+"' and Investigator='"+mon_name+"';";
			try{	
			
			 while(flag==false){
				 ResultSet rs2 = db.executeQuery(sql2);
				if(rs2.next()){
					System.out.println(rs2.getString(1));
					if(rs2.getString(1)!=null){
					 System.out.println("监测点已经存在");
					 System.out.println(rs2.getString(1));
					 flag=true;
					}				
				 }
				else{
					flag=false;
				}
				 if(flag==false){
					 System.out.println("新的监测点");
					 ResultSet rs = db.executeQuery(sql);
					 System.out.println("rs="+rs);
					 if(rs.next()){
						 System.out.println("rs.getString(1)="+rs.getString(1));
							 System.out.println("为新的监测点设置编号");
							 System.out.println("rs.getString(1)="+rs.getString(1));
							 int hh=Integer.parseInt(time[0]);
								int mm=Integer.parseInt(time[1]);
								int ss=Integer.parseInt(time[2]);						
								ss++;
								if(ss/60==1){
									mm++;
									if(mm/60==1){
										hh++;
									}
								}
								ss=ss%60;
								mm=mm%60;
								hh=hh%24;	
								time[0] =Integer.toString(hh);
								if(time[0].length()==1) time[0]="0"+time[0];
								time[1] =Integer.toString(mm);
								if(time[1].length()==1) time[1]="0"+time[1];
								time[2] = Integer.toString(ss);
								if(time[2].length()==1) time[2]="0"+time[2];
								mon_id ="MON"+ ymd[0]+ymd[1]+ymd[2]+time[0]+time[1]+time[2];
					 		}
					 else{
							
							flag=true;
							String sql3  = "insert into MonitoringSite values('"+mon_id+"','长江水产研究所','"+mon_name+"','"+mon_time+"','"+mon_site+"','"+mon_river+"',null,null,null,'"+mon_ltude+"','"+mon_atude+"','"+mon_ltude+"','"+mon_atude+"','"+mon_wht+"','"+mon_tmpt+"','"+account.getID()+"');";
								try{	
								 db.executeUpdate(sql3);
								 rowCounterMoniteringSite++;
								 System.out.println("导入一条数据");
							}catch (Exception e) {
								// TODO: handle exception
								System.out.println("llllll");
								e.printStackTrace();
						     }
					    }	
				 }
				    sql = "select * from MonitoringSite where InverstigationID='"+mon_id+"';";
				    sql2 ="select * from  MonitoringSite where InverstigationID='"+mon_id+"' and Site='"+mon_site+"' and Investigator='"+mon_name+"';";
			 	}	
				
			}catch (Exception e) {
				// TODO: handle exception
				System.out.println("............");
				e.printStackTrace();
	        }				
			
			flag=false;
			String sec_id="SEC"+ ymd[0]+ymd[1]+ymd[2]+time[0]+time[1]+time[2];
			sql = "select * from FractureSurface where ID='"+sec_id+"';";
			sql2 = "select * from FractureSurface where ID='"+sec_id+"' and Position='"+fs_pst+"';";
			try{	
			
			 while(flag==false){	
				 ResultSet rs2 = db.executeQuery(sql2);
					if(rs2.next()){
						System.out.println(rs2.getString(1));
						if(rs2.getString(1)!=null){
						 System.out.println("断面已经存在");
						 System.out.println(rs2.getString(1));
						 flag=true;
						}				
					 }
					else{
						flag=false;
					}
				 if(flag==false){
					 System.out.println("新的断面");
					 ResultSet rs = db.executeQuery(sql);
					 System.out.println("rs="+rs);
						if(rs.next()){
							int hh=Integer.parseInt(time[0]);
							int mm=Integer.parseInt(time[1]);
							int ss=Integer.parseInt(time[2]);						
							ss++;
							if(ss/60==1){
								mm++;
								if(mm/60==1){
									hh++;
								}
							}
							ss=ss%60;
							mm=mm%60;
							hh=hh%24;	
							time[0] =Integer.toString(hh);
							if(time[0].length()==1) time[0]="0"+time[0];
							time[1] =Integer.toString(mm);
							if(time[1].length()==1) time[1]="0"+time[1];
							time[2] = Integer.toString(ss);
							if(time[2].length()==1) time[2]="0"+time[2];
							sec_id ="SEC"+ ymd[0]+ymd[1]+ymd[2]+time[0]+time[1]+time[2];
						}
						else{
							flag=true;
							String sql3  = "insert into FractureSurface values('"+sec_id+"','"+fs_pst+"','"+fs_dtb+"','"+mon_id+"')";
							try{	
								 db.executeUpdate(sql3);
								 rowCounterFractureSurface++;
							}catch (Exception e) {
								// TODO: handle exception
								e.printStackTrace();
							}
					}
			 }
				    sql = "select * from FractureSurface where ID='"+sec_id+"';";
					sql2 = "select * from FractureSurface where ID='"+sec_id+"' and Position='"+fs_pst+"';";
					
			}	
				
			}catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
	        }				
			
			flag=false;
			String phy_id="PHY"+ ymd[0]+ymd[1]+ymd[2]+time[0]+time[1]+time[2];
			sql = "select * from Phytoplankton where SampleID='"+phy_id+"';";
			sql2 = "select * from Phytoplankton where SampleID='"+phy_id+"' and ID_FractureSurface='"+sec_id+"';";
			try{	
			 while(flag==false){	
				 ResultSet rs2 = db.executeQuery(sql2);
					if(rs2.next()){
						System.out.println(rs2.getString(1));					
						 System.out.println("浮游植物已经存在");
						 System.out.println(rs2.getString(1));
						 flag=true;			
					 }
					else{
						flag=false;
					}
					if(flag==false){
						 System.out.println("新的浮游植物");
						 ResultSet rs = db.executeQuery(sql);
						 System.out.println("rs="+rs);
						if(rs.next()){
							int hh=Integer.parseInt(time[0]);
							int mm=Integer.parseInt(time[1]);
							int ss=Integer.parseInt(time[2]);						
							ss++;
							if(ss/60==1){
								mm++;
								if(mm/60==1){
									hh++;
								}
							}
							ss=ss%60;
							mm=mm%60;
							hh=hh%24;	
							time[0] =Integer.toString(hh);
							if(time[0].length()==1) time[0]="0"+time[0];
							time[1] =Integer.toString(mm);
							if(time[1].length()==1) time[1]="0"+time[1];
							time[2] = Integer.toString(ss);
							if(time[2].length()==1) time[2]="0"+time[2];
							phy_id ="PHY"+ ymd[0]+ymd[1]+ymd[2]+time[0]+time[1]+time[2];
						}
						else{
							flag=true;
							String sql3  = "insert into Phytoplankton values('"+phy_id+"',null,null,null,'"+sec_id+"')";
							try{	
								 db.executeUpdate(sql3);
								 rowCounterPhytoplankton++;
							}catch (Exception e) {
								// TODO: handle exception
								e.printStackTrace();
							}
							
					}
					
				    sql = "select * from Phytoplankton where SampleID='"+phy_id+"';";
					sql2 = "select * from Phytoplankton where SampleID='"+phy_id+"' and ID_FractureSurface='"+sec_id+"';";					
			}
			 }	
			}catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
	        }	
			flag=false;
			String pph_id="PPH"+ ymd[0]+ymd[1]+ymd[2]+time[0]+time[1]+time[2];
			sql = "select * from DominantPhytoplanktonSpecies where SampleID='"+pph_id+"';";
			 sql2 = "select * from DominantPhytoplanktonSpecies where SampleID='"+pph_id+"' and Name='"+pph_name+"' and Quality='"+pph_q+"' and Biomass='"+pph_bl+"' and ID_Phytoplankton='"+phy_id+"';";
			  
			try{	
			 
			 while(flag==false){	
				 ResultSet rs2 = db.executeQuery(sql2);
					if(rs2.next()){
						System.out.println(rs2.getString(1));					
						 System.out.println("浮游植物优势种已经存在");
						 System.out.println(rs2.getString(1));
						 flag=true;			
					 }
					else{
						flag=false;
					}
					if(flag==false){
						 ResultSet rs = db.executeQuery(sql);	
						 System.out.println("新的浮游植物优势种");
						
						 System.out.println("rs="+rs);
				
						if(rs.next()){
							int hh=Integer.parseInt(time[0]);
							int mm=Integer.parseInt(time[1]);
							int ss=Integer.parseInt(time[2]);						
							ss++;
							if(ss/60==1){
								mm++;
								if(mm/60==1){
									hh++;
								}
							}
							ss=ss%60;
							mm=mm%60;
							hh=hh%24;	
							time[0] =Integer.toString(hh);
							if(time[0].length()==1) time[0]="0"+time[0];
							time[1] =Integer.toString(mm);
							if(time[1].length()==1) time[1]="0"+time[1];
							time[2] = Integer.toString(ss);
							if(time[2].length()==1) time[2]="0"+time[2];
							pph_id ="PPH"+ ymd[0]+ymd[1]+ymd[2]+time[0]+time[1]+time[2];
						}
						else{
							flag=true;
							String sql3  = "insert into DominantPhytoplanktonSpecies values('"+pph_id+"','"+pph_name+"',null,'"+pph_q+"','"+pph_bl+"','"+phy_id+"')";
							try{	
								 db.executeUpdate(sql3);
								 rowCounterDominantPhytoplanktonSpecies++;
							}catch (Exception e) {
								// TODO: handle exception
								e.printStackTrace();
							}
					}
					
				    sql = "select * from DominantPhytoplanktonSpecies where SampleID='"+pph_id+"';";
				    sql2 = "select * from DominantPhytoplanktonSpecies where SampleID='"+pph_id+"' and Name='"+pph_name+"' and Quality='"+pph_q+"' and Biomass='"+pph_bl+"' and ID_Phytoplankton='"+phy_id+"';";
			}
			 }	
			}catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
	        }				
			LogTool.insertIntoDataLog(LogTool.INSERT_CODE, "MoniteringSite", rowCounterMoniteringSite);
			LogTool.insertIntoDataLog(LogTool.INSERT_CODE, "FractureSurface", rowCounterFractureSurface);
			LogTool.insertIntoDataLog(LogTool.INSERT_CODE, "Phytoplanton", rowCounterPhytoplankton);
			LogTool.insertIntoDataLog(LogTool.INSERT_CODE, "DominantPhytoplanktonSpecies", rowCounterDominantPhytoplanktonSpecies);
			
		}
		   
			
		request.getSession().setAttribute("massage", "导入进程结束");			
		
		
		
			
}
	/**
	 * 关闭资源
	 * 0
	 * @param rs
	 * @param ps
	 * @param conn
	 */
	public void close(ResultSet rs, PreparedStatement ps, Connection conn) {
		try {
			if (rs != null) {
				rs.close();
			}
			if (ps != null) {
				ps.close();
			}
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
}
